﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace DBLayer
{
    public class MySqlDB
    {

        static MySqlConnection mysqlConn;


        /// <summary>
        /// 建立mysql数据库链接
        /// </summary>
        /// <returns></returns>
        public static MySqlConnection CreateMySqlCon()
        {
            String mysqlStr =
                "Database=shopsdb;Data Source=127.0.0.1;User Id=root;Password=root;pooling=false;CharSet=utf8;port=3306";
            //"Database=test;Data Source=192.168.111.129;User Id=root;Password=root;pooling=false;CharSet=utf8;port=3306";
            // String mySqlCon = ConfigurationManager.ConnectionStrings["MySqlCon"].ConnectionString;
            mysqlConn = new MySqlConnection(mysqlStr);
            mysqlConn.Open();
            return mysqlConn;
        }



        public static MySqlConnection getMySqlCon()
        {
            if (mysqlConn == null) CreateMySqlCon();

            if (mysqlConn.State == ConnectionState.Closed)
            {
                mysqlConn.Open();
            }

            return mysqlConn;
        }

        public void CloseConn()
        {
            if (mysqlConn.State == ConnectionState.Open)
            {
                mysqlConn.Close();
            }
        }


        /// <summary>
        /// 建立执行命令语句对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="mysql"></param>
        /// <returns></returns>
        public static MySqlCommand getSqlCommand(String sql)
        {
            MySqlCommand mySqlCommand = new MySqlCommand(sql, getMySqlCon());
            //  MySqlCommand mySqlCommand = new MySqlCommand(sql);
            // mySqlCommand.Connection = mysql;
            return mySqlCommand;
        }


        ///// <summary>
        ///// 查询并获得结果集并遍历
        ///// </summary>
        ///// <param name="mySqlCommand"></param>
        //public static void getResultset(MySqlCommand mySqlCommand)
        //{
        //    MySqlDataReader reader = mySqlCommand.ExecuteReader();
        //    try
        //    {
        //        while (reader.Read())
        //        {
        //            if (reader.HasRows)
        //            {
        //                Console.WriteLine("编号:" + reader.GetInt32(0) + "|姓名:" + reader.GetString(1) + "|年龄:" + reader.GetInt32(2) + "|学历:" + reader.GetString(3));
        //            }
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        Console.WriteLine("查询失败了！");
        //    }
        //    finally
        //    {
        //        reader.Close();
        //    }
        //}

        public int Execute(string SqlString)
        {
            try
            {
                MySqlCommand mySqlCommand = getSqlCommand(SqlString);
                return mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConn();
            }

        }

        public DataTable Select(string SqlString)
        {
            try
            {
                MySqlDataAdapter mySqlDataAdapter = getSqlDataAdapter(SqlString);
                DataTable dt = new DataTable();
                mySqlDataAdapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConn();
            }
        }

        private MySqlDataAdapter getSqlDataAdapter(string SqlString)
        {
            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(SqlString, getMySqlCon());
            return mySqlDataAdapter;
        }




        // public static void batchSql(List<DataObj> list)
        //{
        //    List<string> sqlList = new List<string>();
        //    List<MySqlParameter[]> paraList = new List<MySqlParameter[]>();

        //    foreach (DataObj oh in list)
        //    {
        //        MySqlParameter[] parameters = new MySqlParameter[19];

        //        parameters[0] = new MySqlParameter("@param1", MySqlDbType.VarChar, 50);
        //        parameters[0].Value = oh.param1;
        //        parameters[1] = new MySqlParameter("@param2", MySqlDbType.VarChar, 50);
        //        parameters[1].Value = oh.param2;

        //        sqlList.Add(sqlInsert);
        //        paraList.Add(parameters);
        //    }

        //    ExecuteTransaction(sqlList, paraList);

        //}     

 

       /**
         * 执行事务
         * */
        public void ExecuteTransaction(List<string> sqlList, List<MySqlParameter[]> paraList)
        {
            //Debug.WriteLine(sql);
            using (MySqlConnection connection = getMySqlCon())
            {
                MySqlCommand cmd = new MySqlCommand();
                MySqlTransaction transaction = null;
                cmd.Connection = connection;
                try
                {
                    transaction = connection.BeginTransaction();
                    cmd.Transaction = transaction;

                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        cmd.CommandText = sqlList[i];
                        if (paraList != null && paraList[i] != null)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(paraList[i]);
                        }
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();

                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    {

                    }
                    throw e;
                }
                finally {
                    if(connection != null){
                        connection.Close();
                    }
                }

            }
        }

 

//其中DataObj类定义如下：

 

//class DataObj{

//  string param1;

//  string param2;

//}



    }
}
